package com.etc.traval.daoimpl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.etc.traval.dao.PlaceDao;
import com.etc.traval.dao.PlacecomDao;
import com.etc.traval.dao.UserDao;
import com.etc.traval.util.JDBCUtil;
import com.etc.travel.entity.Place;
import com.etc.travel.entity.Placecom;
import com.etc.travel.entity.User;

import oracle.jdbc.internal.OracleTypes;

public class PlacecomDaoImpl implements PlacecomDao {

	JDBCUtil util = new JDBCUtil();
	UserDao dao = new UserDaoImpl();
	PlaceDao dao1 = new PlaceDaoImpl();

	/**
	 * 查询所有的景区评论
	 * 
	 * @author 刘一清
	 * @return 返回所有评论的集合
	 * @date 2018年5月29日16时42分27秒
	 */
	@Override
	public List<Placecom> queryAllPlacecom(int page, int size) {
		ResultSet rs = util.query(
				"select t.*,t.a from (select s.*,rownum as a  from (select * from t_placecom) s where rownum<=?) t where t.a>?",
				page * size, (page - 1) * size);
		List<Placecom> list = new ArrayList<Placecom>();
		try {
			while (rs.next()) {
				Placecom placecom = new Placecom();
				placecom.setPlacecom_id(rs.getInt("placecom_id"));
				placecom.setPlacecom_place(dao1.queryPlaceById(rs.getInt("placecom_place")));
				placecom.setPlacecom_user(dao.queryUserById(rs.getInt("placecom_user")));
				placecom.setPlacecom_time(rs.getTimestamp("placecom_time"));
				placecom.setPlacecom_content(rs.getString("placecom_content"));
				list.add(placecom);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			util.closeAll();
		}
		return list;
	}

	/**
	 * 根据评论人查找景区评论
	 * 
	 * @author 刘一清
	 * @param 传入User对象
	 * @return 返回查到的评论集合
	 * @date 2018年5月29日16时54分26秒
	 */
	@Override
	public List<Placecom> queryPlacecomByUser(User user, int page, int size) {
		ResultSet rs = util.query(
				"select t.*,t.a from (select s.*,rownum as a  from (select * from t_placecom where placecom_user=? order by placecom_time DESC) s where rownum<=?) t where t.a>?",
				user.getUser_id(), page * size, (page - 1) * size);
		List<Placecom> list = new ArrayList<Placecom>();
		try {
			while (rs.next()) {
				Placecom placecom = new Placecom();
				placecom.setPlacecom_id(rs.getInt("placecom_id"));
				placecom.setPlacecom_place(dao1.queryPlaceById(rs.getInt("placecom_place")));
				placecom.setPlacecom_user(dao.queryUserById(rs.getInt("placecom_user")));
				placecom.setPlacecom_time(rs.getTimestamp("placecom_time"));
				placecom.setPlacecom_content(rs.getString("placecom_content"));
				list.add(placecom);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			util.closeAll();
		}
		return list;
	}

	/**
	 * 根据景点查找景点评论
	 * 
	 * @param 传入Place对象
	 * @return 返回查到的评论集合
	 * @date 2018年5月29日16时57分12秒
	 */
	@Override
	public List<Placecom> queryPlacecomByPlace(Place place, int page, int size) {
		ResultSet rs = util.query(
				"select t.*,t.a from (select s.*,rownum as a  from (select * from t_placecom where placecom_place=?  order by placecom_time DESC) s where rownum<=?) t where t.a>?",
				place.getPlace_id(),page * size, (page - 1) * size);
		List<Placecom> list = new ArrayList<Placecom>();
		try {
			while (rs.next()) {
				Placecom placecom = new Placecom();
				placecom.setPlacecom_id(rs.getInt("placecom_id"));
				placecom.setPlacecom_place(dao1.queryPlaceById(rs.getInt("placecom_place")));
				placecom.setPlacecom_user(dao.queryUserById(rs.getInt("placecom_user")));
				placecom.setPlacecom_time(rs.getTimestamp("placecom_time"));
				placecom.setPlacecom_content(rs.getString("placecom_content"));
				list.add(placecom);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			util.closeAll();
		}
		return list;
	}

	/**
	 * 根据id查找景区评论
	 * 
	 * @author 刘一清
	 * @param 传入要查找的景区评论id
	 * @return 返回查找的Placecom对象
	 * @date 2018年5月29日17时00分29秒
	 */
	@Override
	public Placecom queryPlacecomById(int id) {
		ResultSet rs = util.query("select * from t_placecom where placecom_id=?", id);
		Placecom placecom = new Placecom();
		try {
			while (rs.next()) {
				placecom.setPlacecom_id(rs.getInt("placecom_id"));
				placecom.setPlacecom_place(dao1.queryPlaceById(rs.getInt("placecom_place")));
				placecom.setPlacecom_user(dao.queryUserById(rs.getInt("placecom_user")));
				placecom.setPlacecom_time(rs.getTimestamp("placecom_time"));
				placecom.setPlacecom_content(rs.getString("placecom_content"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			util.closeAll();
		}
		return placecom;
	}

	/**
	 * 根据id删除景区评论
	 * 
	 * @author 刘一清
	 * @param 传入要删除的景区id
	 * @return 返回是否删除成功
	 * @date 2018年5月29日17时03分19秒
	 */
	@Override
	public boolean deletePlacecomById(int id) {
		return util.update("delete from t_placecom where placecom_id=?", id) > 0;
	}

	/**
	 * 添加景区评论
	 * 
	 * @author 刘一清
	 * @param 传入要添加的Placecom对象
	 * @return 返回是否添加成功
	 * @date 2018年5月29日17时07分39秒
	 */
	@Override
	public boolean addPlacecom(Placecom placecom) {
		return util.update(
				"insert into t_placecom (placecom_place,placecom_user,placecom_time,placecom_content) values (?,?,?,?)",
				placecom.getPlacecom_place().getPlace_id(), placecom.getPlacecom_user().getUser_id(),
				placecom.getPlacecom_time(), placecom.getPlacecom_content()) > 0;
	}

	/**
	 * 查询每个景点对应的评论条数
	 * @author 刘一清
	 * @param 传入要查询的景点对象
	 * @return 返回查询到的条数count
	 * @date 2018年5月31日9时28分17秒
	 */
	@Override
	public int queryCountByPlace(Place place) {
		int count = 0;
		ResultSet rs = util.query("select count(*) as countnum from t_placecom where placecom_place=?",
				place.getPlace_id());
		try {
			if (rs.next()) {
				count = rs.getInt("countnum");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	/**
	 * 根据评论热度查找地点
	 * @author cjw
	 * @param size page 每页数量   页码
	 * @return list2 返回集合
	 */
	@Override
	public List<Place> queryPlaceByCom(int size, int page) {
		List<Place> list2 = new ArrayList<Place>();
		List<Integer> list = new ArrayList<Integer>();
		list.add(OracleTypes.CURSOR);
		Object[] obj = util.callHasReturn("{call proc_queryPlaceByCom(?,?,?)}", list,size,page);
		ResultSet rs = (ResultSet) obj[0];
		PlaceDao dao = new PlaceDaoImpl();
		try {
			while (rs.next()) {
				Place place = dao.queryPlaceById(rs.getInt("placecom_place"));
				place.setPlaceComNum(rs.getInt("comnum"));
				list2.add(place);				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list2;
	}
	
}
